import java.sql.*;

/****************************************************************************** 
 * The <code>FinanceDB</code> class takes user inputs and builds MySQL queries to 
 * update and modify the finance application database.  
 * @author Team 4 (Mark Colton, Brian Holley, Stacee Madsen and Chris Smith)
 *****************************************************************************/

public class FinanceDB extends DB
{
    
    /*************************************************************************** 
    * Creates a <code>FinanceDB</code> object 
    **************************************************************************/ 
    public FinanceDB() {
        super();
    }
    
    /*************************************************************************** 
    * Adds a new record to the transaction table.  
    * @param Date A <code>String</code> specifying the date of the transaction.  
    * @param type A <code>String</code> specifying the type of transaction to add. 
    * @param Desc A <code>String</code> specifying the description of the transaction. 
    * @param Amt A <code>double</code> specifying the amount of the transaction.
    * @param Bal A <code>double</code> specifting the updated balance of the register. 
    **************************************************************************/ 

    public void AddTableRecord(String Date, String type, String Desc, double Amt, double Bal) {
        String query = "insert into transaction values";
        query+= " (null, '" + Date + "', '" + type + "', '" + Desc + "', '" + Amt + "', '" + Bal +"')";
        //System.out.println("query = " + query);
        DBedit(query);
    }
    
    /*************************************************************************** 
    * Retrieves a specific record in the transaction table and displays it on the screen.  
    * @param ID An <code>int</code> specifying the transaction number to retrieve.  
    * @return rs A <code>ResultSet</code> showing the returned transaction information.  
    **************************************************************************/ 

    public ResultSet GetTableRecord(int ID) throws Exception {
        String record = "select * from transaction where transId = " + ID;
        //System.out.println("record = " + record);
        ResultSet rs = DBquery(record);
        return rs;
    }
    
    /*************************************************************************** 
    * Edits a <code>String</code> value in the transaction table from a specific transaction ID.  
    * @param ID An <code>int</code> specifying the transaction number to retrieve.
    * @param Col A <code>String</code> specifying the specific column that needs editing.
    * @param newVal A <code>String</code> specifying the new value of the field.  
    **************************************************************************/
    
    public void EditTableRecord(int ID, String Col, String newVal) {
        String editTable = "update transaction set ";
        editTable+= Col + " = '" + newVal + "' where transId = " + ID;
        //System.out.println("editTable =" + editTable);
        DBedit(editTable);
        
    }
    
    /*************************************************************************** 
    * Edits a <code>double</code> value in the transaction table from a specific transaction ID.  
    * @param ID An <code>int</code> specifying the transaction number to retrieve.
    * @param Col A <code>String</code> specifying the specific column that needs editing.
    * @param newVal A <code>double</code> specifying the new value of the field.  
    **************************************************************************/
    
    public void EditTableRecord(int ID, String Col, double newVal) {
        String editTable = "update transaction set ";
        editTable+= Col + " = " + newVal + "where transId = " + ID;
        //System.out.println("editTable =" + editTable);
        DBedit(editTable);
        
    }
    
    /*************************************************************************** 
    * Retrieves the last transaction ID in the transaction table.  
    * @return ID A <code>int</code> specifying the returned max transaction ID.  
    **************************************************************************/ 

    public int GetMaxID() throws Exception {
        String query = "select max(transId) from transaction";
        //System.out.println("query = " + query);
        ResultSet rs = DBquery(query);
        int ID = 0;
        while (rs.next()) {
            ID = rs.getInt(1);
        }
        return ID;
    }
    
    /*************************************************************************** 
    * Deletes a record in the transaction table.  
    * @param ID An <code>int</code> specifying the transaction number to delete.
    **************************************************************************/ 
    
    public void DeleteTableRecord(int ID) {
        String delRec = "delete from transaction where transId = " + ID;
        DBedit(delRec);
        
    }
    
    /*************************************************************************** 
    * Retrieves all records in the transaction table and displays them on the screen.  
    * @return rs A <code>ResultSet</code> showing all the returned transactions in the table.  
    **************************************************************************/ 
    
    public ResultSet GetTable() throws Exception {
        String allTable = "select * from transaction";
        //System.out.println("allTable = " + allTable);
        ResultSet rs = DBquery(allTable);
        return rs;
        
    }
    
    /*************************************************************************** 
    * Begin of the FinanceDB class. 
    * @param args A <code>String array</code> not used by this application. 
    **************************************************************************/ 

    public static void main(String [] args) throws Exception {
     FinanceDB newDB = new FinanceDB();
     ResultSet rs;
     rs = newDB.GetTable();
     while(rs.next()) {
         System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4) 
         + "\t" + rs.getDouble(5) + "\t" + rs.getDouble(6));
     }
     
 /*    newDB.AddTableRecord("14-Mar-2008","D", "check", 1020, 2250);
     rs = newDB.GetTable();
     while(rs.next()) {
         System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4) 
         + "\t" + rs.getDouble(5) + "\t" + rs.getDouble(6));       
     
     }
     newDB.EditTableRecord(4, "description", "Chris Paycheck");
     
     rs = newDB.GetTable();
     while(rs.next()) {
         System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4) 
         + "\t" + rs.getDouble(5) + "\t" + rs.getDouble(6));
     }
     
     newDB.EditTableRecord(4, "balance", 20.00);
     rs = newDB.GetTable();
     while(rs.next()) {
         System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4) 
         + "\t" + rs.getDouble(5) + "\t" + rs.getDouble(6));
     }
     
     newDB.DeleteTableRecord(5);
     rs = newDB.GetTable();
     while(rs.next()) {
         System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4) 
         + "\t" + rs.getDouble(5) + "\t" + rs.getDouble(6));       
     
     }*/
     
    // int ID = newDB.GetMaxID();
     //System.out.print("ID = " + ID);
        
    }
}
